package com.apes.fn.scm.merchant.settlement.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.scm.retailOrder.repository.ReportDaliyDao;
import com.apes.fn.scm.retailOrder.service.RclDaliyReportService;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.io.ObjectInputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.*;

/**
 * @BelongsProject: apes-server
 * @BelongsPackage: com.apes.fn.scm.merchant.settlement.repository
 * @ClassName: MerchantSettlementDao
 * @Author: pyb
 * @Description:
 * @CreateTime: 2020-11-19 10:58
 * @Version: 1.0
 */
@Component
public class MerchantSettlementDao extends ApesDao {

    EntityManager entityManager;

    @Autowired
    private void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Autowired
    ReportDaliyDao reportDaliyDao;
    @Autowired
    RclDaliyReportService rclDaliyReportService;

    /**
     * 查询应收账款(内)明细
     *
     * @return
     */
    public List<Map> find_receivableInMx(Date reconcileBeginDate, Date reconcileEndDate, String merchantId, String DEPT_ID, String account_type) {

        String sb = "SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, DEPT_NAME, COMPANY_ID, COMPANY_NAME, ACCOUNTTYPE,  " +
                "TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS FROM (SELECT B.AMOUNT, C.DEPT_ID,  " +
                "(SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME, C.COMPANY_ID, (SELECT C.NAME  " +
                "FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = C.COMPANY_ID) COMPANY_NAME,  " +
                "'集群引流' accountType, C.DATE_APPROVE FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C WHERE  " +
                "B.RETAIL_ORDER_ID = C.ID AND B.PAYMENT_METHOD_TYPE IN ('09') AND C.CLAIM_TYPE IS NULL AND (  \n" +
                "                                     (B.CUSTOMER_ID IN ('K1001','K1003','C0005998','02532856','02533043') AND C.ORDER_TYPE<>'replacement' )  \n" +
                "                                         or  \n" +
                "                                     (  \n" +
                "                                         ( EXISTS(SELECT 1  \n" +
                "                                                                           FROM MAS_PARTY_ROLE R  \n" +
                "                                                                           WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID  \n" +
                "                                                                                               FROM MAS_PARTY_ROLE MPR  \n" +
                "                                                                                               WHERE MPR.ID = B.CUSTOMER_ID)  \n" +
                "                                                                             and R.ROLE_ID = 'Company')  \n" +
                "                                         )  \n" +
                "                                             AND C.ORDER_TYPE='replacement'  \n" +
                "                                             AND ((SELECT t.Party_Id  \n" +
                "                                           FROM MAS_PARTY_ROLE t  \n" +
                "                                           WHERE T.ID = C.COMPANY_ID) !=  \n" +
                "                                          (SELECT t.Party_Id  \n" +
                "                                           FROM MAS_PARTY_ROLE t  \n" +
                "                                           WHERE T.ID = B.CUSTOMER_ID))  \n" +
                "                                     )  \n" +
                "                                 )" +
                "                   AND NOT EXISTS(SELECT 1\n" +
                "                   FROM RETAIL_ORDER_ITEM E\n" +
                "                   WHERE E.PRODUCT_ID IN ('0002935068', '0002902247', '0002935072', '0003020877', '0002902250')\n" +
                "                     AND E.SALE_ID = C.ID)  " +
                " UNION ALL SELECT B.AMOUNT, C.DEPT_ID, (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID)  " +
                "DEPT_NAME, C.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = C.COMPANY_ID) COMPANY_NAME, '其他' accountType, C.DATE_APPROVE FROM ADVANCE_PAYMENT_METHOD  " +
                "B, ADVANCE_PAYMENT C WHERE B.payment_id = C.ID AND C.STATE = 'approve' AND B.PAYMENT_METHOD_TYPE  " +
                "IN ('09') AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              )) AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID)) UNION ALL SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID, (SELECT  " +
                "NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME, A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY  " +
                "C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D .ID = A.COMPANY_ID) COMPANY_NAME, '平台内客户'  " +
                "accountType , A.CREATE_DATE DATE_APPROVE FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B WHERE A.ID  " +
                "= B.VOUCHER_ID AND A.VOUCHER_TYPE = 'CUSTOMER_SALE' AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE  " +
                "t WHERE T.ID = A.COMPANY_ID) != (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.PARTNER_ID))  " +
                "AND A.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss' AND NVL(A.CLASSIFICATION,' ') not in ('CROSS_IC','CROSS_IC_RETURN')" +
                "UNION ALL SELECT AMOUNT, A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,  " +
                "A.PAYMENT_ID AS COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID =  " +
                "D.PARTY_ID AND D.ID = A.PAYMENT_ID) COMPANY_NAME, '接案介绍费' accountType , A.CREATE_DATE DATE_APPROVE  " +
                "FROM FN_SETTLE_EXPENSE A WHERE A.EXPENSE_TYPE = 'infoExpense' AND A.TYPE = 'in'UNION ALL SELECT  " +
                "B.FN_COMMISSION AS AMOUNT , A.DEPT_ID AS DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)  " +
                "DEPT_NAME, A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = A.COMPANY_ID) COMPANY_NAME, '车险佣金' accountType, A.CREATE_DATE as DATE_APPROVE FROM  " +
                "POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B WHERE A.ID = B.POLICY_INFORMATION_ID UNION  " +
                "ALL SELECT A.STORE_PRICE AS AMOUNT,A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)  " +
                "DEPT_NAME, B.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = B.COMPANY_ID) COMPANY_NAME, '二手车服务费' accountType,A.date_approve as DATE_APPROVE  " +
                "FROM WAR_USED_CAR A ,MAS_DEPT B WHERE A.DEPT_ID = B.ID AND A.STATE = 'approve' ) WHERE 1 =  " +
                "1 AND COMPANY_ID = :merchantId AND DEPT_ID =:DEPT_ID AND DATE_APPROVE >= :reconcileBeginDate  " +
                "AND DATE_APPROVE < :reconcileEndDate AND ACCOUNTTYPE = :account_type GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,ACCOUNTTYPE,TO_CHAR(DATE_APPROVE,  " +
                "'yyyy-MM-dd') order BY TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("DEPT_ID", DEPT_ID);
        query.setParameter("account_type", account_type);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList;
    }

    /**
     * 查询应收账款(内)分类明细
     *
     * @return
     */
    public List<Map> find_receivableInMxType(Date reconcileBeginDate, Date reconcileEndDate, String merchantId, String DEPT_ID) {

        String sb = "SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID, DEPT_NAME, COMPANY_ID, COMPANY_NAME, ACCOUNTTYPE,  " +
                "TO_CHAR(:reconcileBeginDate ,'yyyy-MM-dd')  AS START_DATE , TO_CHAR(:reconcileEndDate -1 ,'yyyy-MM-dd') AS END_DATE FROM (SELECT B.AMOUNT, C.DEPT_ID,  " +
                "(SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME, C.COMPANY_ID, (SELECT C.NAME  " +
                "FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = C.COMPANY_ID) COMPANY_NAME,  " +
                "'集群引流' accountType, C.DATE_APPROVE FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C WHERE  " +
                "B.RETAIL_ORDER_ID = C.ID AND B.PAYMENT_METHOD_TYPE IN ('09') AND C.CLAIM_TYPE IS NULL AND (  \n" +
                "                                     (B.CUSTOMER_ID IN ('K1001','K1003','C0005998','02532856','02533043') AND C.ORDER_TYPE<>'replacement' )  \n" +
                "                                         or  \n" +
                "                                     (  \n" +
                "                                         (EXISTS(SELECT 1  \n" +
                "                                                                           FROM MAS_PARTY_ROLE R  \n" +
                "                                                                           WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID  \n" +
                "                                                                                               FROM MAS_PARTY_ROLE MPR  \n" +
                "                                                                                               WHERE MPR.ID = B.CUSTOMER_ID)  \n" +
                "                                                                             and R.ROLE_ID = 'Company')  \n" +
                "                                         )  \n" +
                "                                             AND C.ORDER_TYPE='replacement'  \n" +
                "                                             AND ((SELECT t.Party_Id  \n" +
                "                                           FROM MAS_PARTY_ROLE t  \n" +
                "                                           WHERE T.ID = C.COMPANY_ID) !=  \n" +
                "                                          (SELECT t.Party_Id  \n" +
                "                                           FROM MAS_PARTY_ROLE t  \n" +
                "                                           WHERE T.ID = B.CUSTOMER_ID))  \n" +
                "                                     )  \n" +
                "                                 ) AND NOT EXISTS(SELECT 1\n" +
                "                   FROM RETAIL_ORDER_ITEM E\n" +
                "                   WHERE E.PRODUCT_ID IN ('0002935068', '0002902247', '0002935072', '0003020877', '0002902250')\n" +
                "                     AND E.SALE_ID = C.ID)   " +
                " UNION ALL SELECT B.AMOUNT, C.DEPT_ID, (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID)  " +
                "DEPT_NAME, C.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = C.COMPANY_ID) COMPANY_NAME, '其他' accountType, C.DATE_APPROVE FROM ADVANCE_PAYMENT_METHOD  " +
                "B, ADVANCE_PAYMENT C WHERE B.payment_id = C.ID AND C.STATE = 'approve' AND B.PAYMENT_METHOD_TYPE  " +
                "IN ('09') AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))  AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID)) UNION ALL SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID, (SELECT  " +
                "NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME, A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY  " +
                "C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = A.COMPANY_ID) COMPANY_NAME, '平台内客户'  " +
                "accountType , A.CREATE_DATE DATE_APPROVE FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B WHERE A.ID  " +
                "= B.VOUCHER_ID AND A.VOUCHER_TYPE = 'CUSTOMER_SALE' AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE  " +
                "t WHERE T.ID = A.COMPANY_ID) != (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.PARTNER_ID))  AND A.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss' AND NVL(A.CLASSIFICATION,' ') NOT IN ('CROSS_IC','CROSS_IC_RETURN')" +
                "UNION ALL SELECT AMOUNT, A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,  " +
                "A.PAYMENT_ID AS COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID =  " +
                "D.PARTY_ID AND D.ID = A.PAYMENT_ID) COMPANY_NAME, '接案介绍费' accountType , A.CREATE_DATE DATE_APPROVE  " +
                "FROM FN_SETTLE_EXPENSE A WHERE A.EXPENSE_TYPE = 'infoExpense' AND A.TYPE = 'in'UNION ALL SELECT  " +
                "B.FN_COMMISSION AS AMOUNT , A.DEPT_ID AS DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)  " +
                "DEPT_NAME, A.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = A.COMPANY_ID) COMPANY_NAME, '车险佣金' accountType, A.CREATE_DATE as DATE_APPROVE FROM  " +
                "POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B WHERE A.ID = B.POLICY_INFORMATION_ID UNION  " +
                "ALL SELECT A.STORE_PRICE AS AMOUNT,A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)  " +
                "DEPT_NAME, B.COMPANY_ID, (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID  " +
                "AND D.ID = B.COMPANY_ID) COMPANY_NAME, '二手车服务费' accountType,A.date_approve as DATE_APPROVE  " +
                "FROM WAR_USED_CAR A ,MAS_DEPT B WHERE A.DEPT_ID = B.ID AND A.STATE = 'approve' ) WHERE 1 =  " +
                "1 AND COMPANY_ID = :merchantId AND DEPT_ID =:DEPT_ID AND DATE_APPROVE >= :reconcileBeginDate  " +
                "AND DATE_APPROVE < :reconcileEndDate GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,ACCOUNTTYPE order BY DEPT_ID ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("DEPT_ID", DEPT_ID);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList;
    }

    /**
     * 查询资金分类明细
     *
     * @return
     */
    public List<Map> findPaymentMxType(Date reconcileBeginDate, Date reconcileEndDate, String merchantId, String DEPT_ID) {

        String sb = "SELECT COMPANY_ID,\n" +
                "       DEPT_ID,\n" +
                "       payment_method_type,\n" +
                "       NVL(SUM(AMOUNT + DS_AMT), 0)            AMT,\n" +
                "       NVL(SUM(YS_AMT), 0)                     YS_AMT,\n" +
                "       NVL(SUM(ZJSR_AMT), 0)                   ZJSR_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT), 0) ZJ_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT + DS_AMT), 0)\n" +
                "                                               AMOUNT,\n" +
                "       NVL(SUM(DS_AMT), 0)                     DS_AMT,\n" +
                "       TO_CHAR(:reconcileBeginDate ,'yyyy-MM-dd')                    DATE_OF_STATISTICS_STATE,\n" +
                "       TO_CHAR(:reconcileEndDate -1 ,'yyyy-MM-dd')                      DATE_OF_STATISTICS_END,\n" +
                "       DEPT_NAME,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       COMPANY_NAME\n" +
                "FROM (SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)                             DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                                                  COMPANY_NAME,\n" +
                "             B.AMOUNT,\n" +
                "             0                                                                            DS_AMT,\n" +
                "             0                                                                            YS_AMT,\n" +
                "             0                                                                            ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04', '10',\n" +
                "                                          '12', '05', '09', '003')\n" +
                "        AND C.ORDER_TYPE NOT IN ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" +
                "      UNION ALL\n" +
                "      SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)\n" +
                "                                         DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE)\n" +
                "                                         PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "             B.AMOUNT,\n" +
                "             0                           DS_AMT,\n" +
                "             0                           YS_AMT,\n" +
                "             0                           ZJSR_AMT\n" +
                "      FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04', '10', '12', '05', '09', '003')\n" +
                "      UNION ALL\n" +
                "      SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)                             DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                                                  COMPANY_NAME,\n" +
                "             0                                                                            AMOUNT,\n" +
                "             0                                                                            DS_AMT,\n" +
                "             B.AMOUNT                                                                     YS_AMT,\n" +
                "             0                                                                            ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND C.ORDER_TYPE\n" +
                "          NOT IN ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" +
                "      UNION ALL\n" +
                "      SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME\n" +
                "              FROM mas_payment_method t\n" +
                "              WHERE T.ID = B.PAYMENT_METHOD_TYPE)             PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                      COMPANY_NAME,\n" +
                "             0                                                AMOUNT,\n" +
                "             0                                                DS_AMT,\n" +
                "             B.AMOUNT                                         YS_AMT,\n" +
                "             0                                                ZJSR_AMT\n" +
                "      FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE\n" +
                "          IN ('09')\n" +
                "      UNION ALL\n" +
                "      SELECT A.Create_Date          DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             (SELECT NAME\n" +
                "              FROM MAS_DEPT\n" +
                "              WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "             ''                     PAYMENT_METHOD_TYPE,\n" +
                "             ''                     PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = A.COMPANY_ID)\n" +
                "                                    COMPANY_NAME,\n" +
                "             0                      AMOUNT,\n" +
                "             0                      DS_AMT,\n" +
                "             B.PRICE_TOTAL          YS_AMT,\n" +
                "             0                      ZJSR_AMT\n" +
                "      FROM ACC_VOUCHER a,\n" +
                "           ACC_VOUCHER_ITEM B\n" +
                "      WHERE A.ID = B.VOUCHER_ID\n" +
                "        AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "        AND a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss'\n" +
                "        AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.COMPANY_ID) != (SELECT t.Party_Id\n" +
                "                                                                                     FROM MAS_PARTY_ROLE t\n" +
                "                                                                                     WHERE T.ID = A.PARTNER_ID))\n" +
                "      UNION ALL\n" +
                "      SELECT A.Create_Date                                    DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "             ''                                               PAYMENT_METHOD_TYPE,\n" +
                "             ''                                               PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.PAYMENT_ID                                     COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = A.PAYMENT_ID)                      COMPANY_NAME,\n" +
                "             0                                                AMOUNT,\n" +
                "             0                                                DS_AMT,\n" +
                "             A.AMOUNT\n" +
                "                                                              YS_AMT,\n" +
                "             0                                                ZJSR_AMT\n" +
                "      FROM FN_SETTLE_EXPENSE A\n" +
                "      WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "        AND A.TYPE\n" +
                "          = 'in'\n" +
                "      UNION ALL\n" +
                "      SELECT C.APPROVE_DATE         DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME\n" +
                "              FROM MAS_DEPT\n" +
                "              WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             E.PAYMENT_METHOD_TYPE,\n" +
                "             M.name                 PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)\n" +
                "                                    COMPANY_NAME,\n" +
                "             0                      AMOUNT,\n" +
                "             0                      DS_AMT,\n" +
                "             0                      YS_AMT,\n" +
                "             E.INCOME_AMOUNT        ZJSR_AMT\n" +
                "      from (SELECT A.ID DEPT_ID,\n" +
                "                   B.ID IMCOME_ID,\n" +
                "                   B.APPROVE_DATE,\n" +
                "                   B.COMPANY_ID\n" +
                "            FROM MAS_DEPT A,\n" +
                "                 MONEY_INCOME B\n" +
                "            WHERE A.COMPANY_ID\n" +
                "                = B.COMPANY_ID\n" +
                "              AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "              AND B.STATE = 'approve') C,\n" +
                "           MONEY_INCOME_ITEM E,\n" +
                "           Mas_Payment_Method M\n" +
                "      WHERE C.IMCOME_ID = E.MONEY_INCOME\n" +
                "        AND E.PAYMENT_METHOD_TYPE = M.ID\n" +
                "      UNION ALL\n" +
                "      SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)\n" +
                "                                         DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE)\n" +
                "                                         PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "             0                           AMOUNT,\n" +
                "             B.AMOUNT                    DS_AMT,\n" +
                "             0                           YS_AMT,\n" +
                "             0                           ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04', '10', '12', '05', '09', '003')\n" +
                "        AND C.ORDER_TYPE IN ('agencyPay',\n" +
                "                             'noDirectPay', 'directPay', 'DS0'))\n" +
                "WHERE 1 = 1\n" +
                "  and PAYMENT_METHOD_TYPE NOT IN ('09')\n" +
                "  AND COMPANY_ID\n" +
                "    = :merchantId\n" +
                "  AND DEPT_ID = :DEPT_ID AND DATE_APPROVE >= :reconcileBeginDate AND DATE_APPROVE <:reconcileEndDate\n" +
                "GROUP BY DEPT_NAME, COMPANY_ID, DEPT_ID, payment_method_type, PAYMENT_METHOD_TYPE_NAME,\n" +
                "         COMPANY_NAME\n" +
                "order BY DEPT_ID ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("DEPT_ID", DEPT_ID);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList;
    }

    /**
     * 查找成本(业务凭证)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findVoucherCosts(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }


    /**
     * 查找成本(业务凭证)的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findVoucherCostsCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCountSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查找经销成本(业务凭证)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findOwnVoucherCosts(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getOwnSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }


    /**
     * 查找经销成本(业务凭证)的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findOwnVoucherCostsCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCountOwnSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查找经销成本(供应商账单)的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findOwnInvoiceCostsCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, String inventoryMode) {
        String sb = "SELECT COUNT(1) COUNT,sum(PRICE_UNIT) PRICE_UNIT,sum(QTY) QTY,sum(PRICE_SUBTOTAL) PRICE_SUBTOTAL,sum(DISCOUNT) DISCOUNT FROM ( " +
                getCountInvoiceOwnSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("inventoryMode", inventoryMode);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 查找成本(业务凭证)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findConsignVoucherCosts(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getConsignSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 查找经销成本(供应商账单)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findOwnInvoiceCosts(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, String inventoryMode, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getInvoiceOwnSql() +
                " ) CB  WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("inventoryMode", inventoryMode);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 查找代销成本(业务凭证)的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findConsignVoucherCostsCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCountConsignSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询应收账款(外)明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findAccountsReceivable(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        //取结果集
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getAccountSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询应收账款(外)的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findAccountsReceivableCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getAccountSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }


    /**
     * 查询营业款明细
     *
     * @param requestJO
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @param page
     * @param size
     * @return
     */
    public List<Map> findBusinessMoney(JSONObject requestJO, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getBusinessMoneyString() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", requestJO.getString("merchantId"));
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return (List<Map>) query.getResultList();
    }

    /**
     * 查询营业款明细的总页数
     *
     * @param requestJO
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findBusinessMoneyCount(JSONObject requestJO, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getBusinessMoneyString() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", requestJO.getString("merchantId"));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }


    /**
     * 查询委托定损成本明细
     *
     * @param requestJO
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @param page
     * @param size
     * @return
     */
    public List<Map> findLossAssessmentCost(JSONObject requestJO, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getLossAssessmentCostString() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", requestJO.getString("merchantId"));
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return (List<Map>) query.getResultList();
    }

    /**
     * 委托定损成本明细的总页数
     *
     * @param requestJO
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findLossAssessmentCostCount(JSONObject requestJO, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getLossAssessmentCostString() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", requestJO.getString("merchantId"));
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询供应链管理费明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @param page
     * @param size
     * @return
     */
    public List<Map> findManagementFee(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getManagementFeSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return (List<Map>) query.getResultList();
    }

    /**
     * 查询供应链管理费的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findManagementFeCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getManagementFeCountSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }


    /**
     * 查询供应链管理费
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public double findCurrentPeriodShouldDeducted(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "select NVL(sum(SUPPLY_CHAIN_MANAGEMENT_FEE),0) SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "from ( " +
                "select I.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "   FROM ACC_VOUCHER T " +
                "                      LEFT JOIN ACC_VOUCHER_ITEM I " +
                "                                ON T.ID = I.VOUCHER_ID " +
                "              WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE " +
                "                 < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "               AND T.VOUCHER_TYPE " +
                "                 = 'SUPPLIER_PURCHASE' " +
                "              AND (CLASSIFICATION not in ('05', '06')or CLASSIFICATION is null ) " +
                "               AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "               AND T.INVENTORY_MODE = 'IM_OWN' " +
                "               AND I.PRICE_TOTAL <> 0 " +
                " " +
                "UNION ALL " +
                "select I.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "   FROM ACC_VOUCHER T " +
                "                      LEFT JOIN ACC_VOUCHER_ITEM I  ON T.ID = I.VOUCHER_ID " +
                "             WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE " +
                "                 < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "               AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME' " +
                "              AND (CLASSIFICATION not in ('05', '06')or CLASSIFICATION is null ) " +
                "               AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "               AND T.INVENTORY_MODE = 'IM_CONSIGN' AND I.PRICE_TOTAL <> 0" +
                " " +
                "UNION ALL " +
                "SELECT I.SUPPLY_CHAIN_MANAGEMENT_FEE " +
                "   FROM ACC_VOUCHER T " +
                "                      LEFT JOIN ACC_VOUCHER_ITEM I  ON T.ID = I.VOUCHER_ID " +
                "                      LEFT JOIN MAS_STORE D ON T.DEPT_ID = D.ID,V_SERVE_TYPE S" +
                "             WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE " +
                "                 < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "               AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE'\n" +
                "               AND T.SCENE_ID = 'SR004'\n" +
                "               AND T.INVENTORY_MODE = 'IM_OWN'\n" +
                "               AND I.PRICE_TOTAL <> 0\n" +
                "               AND D.STORE_TYPE_NAME = '001171'\n" +
                "               AND PARTNER_ID = 'A1001'\n" +
                "               AND S.PRODUCTID = I.PRODUCT_ID\n" +
                "               AND S.SERVE_TYPE = '007'" +
                ")";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return Double.parseDouble(resultList.get(0).get("SUPPLY_CHAIN_MANAGEMENT_FEE").toString());
    }

    /**
     * 根据渠道查询平台费(合计)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public double findSumPlatformFeeByChannel(String merchantId, String channelId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT nvl(SUM(CASE WHEN CHANNEL_ID='100' THEN\n" +
                "    TMALL_PLATFORM_FEE ELSE CXY_PLATFORM_FEE END\n" +
                "    ),0) FEE  " +
                "FROM Retail_Order_Payment_Method T " +
                "         LEFT JOIN RETAIL_ORDER L ON T.RETAIL_ORDER_ID = L.ID " +
                "WHERE L.CHANNEL_ID =:channelId AND L.COMPANY_ID =:merchantId AND DATE_APPROVE>= :reconcileBeginDate AND DATE_APPROVE< :reconcileEndDate";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("channelId", channelId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return Double.parseDouble(resultList.get(0).get("FEE").toString());
    }

    /**
     * 根据渠道查询付款明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findPlatformFeeByChannel(String merchantId, String channelId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getChannelSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("channelId", channelId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return (List<Map>) query.getResultList();
    }

    /**
     * 根据渠道查询付款明细的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findPlatformFeeByChannelCount(String merchantId, String channelId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getChannelCountSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("channelId", channelId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询定损管理费明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findQuotationExpense(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getQuotationExpenseSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询定损管理费明细的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findQuotationExpenseCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getQuotationExpenseCountSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }


    /**
     * 查询会员跨公司消费-收入
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findCrossCompanyIncome(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        //取结果集
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getCrossCompanyIncomeSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询会员跨公司消费-收入的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findCrossCompanyIncomeCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCrossCompanyIncomeSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询会员跨公司消费-收入的总计
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public double findCrossCompanyIncomeSum(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT nvl(SUM(TOTAL),0) TOTAL FROM ( " +
                getCrossCompanyIncomeSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return Double.parseDouble(resultList.get(0).get("TOTAL").toString());
    }


    /**
     * 查询会员跨公司消费-支出
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findCrossCompanyPay(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        //取结果集
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getCrossCompanyPaySql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询会员跨公司消费-支出的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findCrossCompanyPayCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCrossCompanyPaySql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }


    /**
     * 查询会员跨公司消费-支出的总计
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public double findCrossCompanyPaySum(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT nvl(SUM(TOTAL),0) TOTAL FROM ( " +
                getCrossCompanyPaySql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return Double.parseDouble(resultList.get(0).get("TOTAL").toString());
    }


    /**
     * 查询平台服务费减免(合计)
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public double findSumFeeReducedDetail(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT NVL(SUM(I.PLATFORM_SERVICE_FEE_REDUCED) ,0) FEE\n" +
                "FROM ACC_VOUCHER T LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "WHERE VOUCHER_TYPE='CUSTOMER_SALE' AND I.PLATFORM_SERVICE_FEE_REDUCED <>0 AND T.COMPANY_ID=:merchantId AND T.CREATE_DATE >= :reconcileBeginDate  AND T.CREATE_DATE < :reconcileEndDate ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return Double.parseDouble(resultList.get(0).get("FEE").toString());
    }

    /**
     * 查询平台服务费减免明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findFeeReducedDetail(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getFeeReducedDetailSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询平台服务费减免的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findFeeReducedDetailCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getFeeReducedDetailSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询平台服务费减免明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findFeeReducedItem(String merchantId, String productClassify, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getFeeReducedItemSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("PRODUCT_CLASSIFY", productClassify);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }


    /**
     * 查询内部销售服务费减免的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findInternalDetailCount(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getInternalDetailSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询内部销售服务费减免明细
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> findInternalDetail(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getInternalDetailSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        return getResults(merchantId, reconcileBeginDate, reconcileEndDate, page, size, sb);
    }

    /**
     * 查询平台服务费减免的总页数
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public Object findFeeReducedItemCount(String merchantId, String productClassify, Date reconcileBeginDate, Date reconcileEndDate) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getFeeReducedItemSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("PRODUCT_CLASSIFY", productClassify);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 查询定损管理费明细SQL
     *
     * @return
     */
    private String getQuotationExpenseSql() {
        return "     SELECT A.ID, CASE\n" +
                "           WHEN A.TYPE = 'out' THEN\n" +
                "               A.AMOUNT\n" +
                "           WHEN A.TYPE = 'in' THEN A.AMOUNT * -1\n" +
                "           END AMOUNT,TO_CHAR( A.CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE,  " +
                "                           A.LOSS_QUOTATION_AMT, A.ORIGIN,  " +
                "                           (case when A.type='in' then '收入' else '支出' end) TYPE,  " +
                "                           case when A.EXPENSE_TYPE='infoExpense' then '接案介绍费' " +
                "                     when A.EXPENSE_TYPE='platformExpense' then '平台管理费' else '定损管理费' end as EXPENSE_TYPE, " +
                "                        (case when A.ORIGIN_BUSINESS_TYPE='LossQuotation' then '定损单' else '老系统定损单' end) ORIGIN_BUSINESS_TYPE  ,  " +
                "                        (case when A.SOURCE_BUSINESS_TYPE='RetailOrder' then '零售单' else A.SOURCE_BUSINESS_TYPE end) SOURCE_BUSINESS_TYPE ,  " +
                "                           A.PAYMENT_ID, A.TENANT_ID,A.REMARK, A.SOURCE,   " +
                "                           GET_PARTY_NAME(A.TENANT_ID, 'Company') TENANT_NAME,  " +
                "                           GET_PARTY_NAME(A.PAYMENT_ID, 'Company') PAYMENT_NAME,  " +
                "                           (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,  " +
                "                           (SELECT DESCRIPTION FROM MAS_ENUMERATION WHERE ID = A.CASE_TYPE) CASE_TYPE,  " +
                "                           A.CAR_LICENSE  " +
                "                      FROM FN_SETTLE_EXPENSE A where ((A.TYPE = 'out' AND A.EXPENSE_TYPE != 'platformExpense') or (A.TYPE = 'in' AND A.EXPENSE_TYPE = 'platformExpense')) AND A.PAYMENT_ID=:merchantId AND  A.CREATE_DATE>=:reconcileBeginDate and A.CREATE_DATE<:reconcileEndDate ORDER BY A.ID";
    }

    /**
     * 查询定损管理费明细总数SQL
     *
     * @return
     */
    private String getQuotationExpenseCountSql() {
        return "  SELECT A.ID FROM FN_SETTLE_EXPENSE A where ((A.TYPE = 'out' AND A.EXPENSE_TYPE != 'platformExpense') or (A.TYPE = 'in' AND A.EXPENSE_TYPE = 'platformExpense')) AND A.PAYMENT_ID=:merchantId AND  A.CREATE_DATE>=:reconcileBeginDate and A.CREATE_DATE<:reconcileEndDate ORDER BY A.ID ";
    }

    /**
     * 查询渠道平台费明细SQL
     *
     * @return
     */
    private String getChannelSql() {
        return "SELECT  TO_CHAR(L.DATE_APPROVE, 'YYYY-MM-DD') AS DATE_APPROVE, " +
                "       L.ID         AS                                                              ID, " +
                "       L.COMPANY_ID AS                                                              COMPANY_ID, " +
                "       (SELECT C.NAME " +
                "        FROM MAS_PARTY C, " +
                "             MAS_PARTY_ROLE D " +
                "        WHERE C.ID = D.PARTY_ID " +
                "          AND D.ID = L.COMPANY_ID)                                                  COMPANY_NAME, " +
                "       (SELECT NAME FROM MAS_DEPT A WHERE A.ID = L.DEPT_ID)                         DEPT_NAME, " +
                "       (SELECT C.NAME FROM MAS_PAYMENT_METHOD C WHERE T.PAYMENT_METHOD_TYPE = C.ID) PAYMENT_METHOD_NAME, " +
                "       T.AMOUNT, " +
                "       (CASE WHEN CHANNEL_ID='100' THEN\n" +
                "        T.TMALL_PLATFORM_FEE ELSE T.CXY_PLATFORM_FEE END\n" +
                "    ) FEE " +
                "FROM Retail_Order_Payment_Method T " +
                "         LEFT JOIN RETAIL_ORDER L ON T.RETAIL_ORDER_ID = L.ID " +
                "WHERE l.CHANNEL_ID =:channelId AND L.COMPANY_ID =:merchantId AND DATE_APPROVE>= :reconcileBeginDate AND DATE_APPROVE< :reconcileEndDate ORDER BY L.ID";
    }

    /**
     * 根据渠道查询付款明细的总页数SQL
     *
     * @return
     */
    private String getChannelCountSql() {
        return "SELECT T.ID " +
                "FROM Retail_Order_Payment_Method T " +
                "         LEFT JOIN RETAIL_ORDER L ON T.RETAIL_ORDER_ID = L.ID " +
                "WHERE l.CHANNEL_ID =:channelId AND L.COMPANY_ID =:merchantId AND DATE_APPROVE>= :reconcileBeginDate AND DATE_APPROVE< :reconcileEndDate ORDER BY L.ID";
    }

    /**
     * 营业款sql
     *
     * @return
     */
    private String getBusinessMoneyString() {
        return "SELECT A.COMPANY_ID,:reconcileBeginDate AS START_DATE , :reconcileEndDate AS END_DATE, D.NAME COMPANY_NAME, A.DEPT_ID,(SELECT NAME FROM MAS_DEPT WHERE ID= A.DEPT_ID) " +
                "DEPT_NAME,NVL(SUM(SALE_MONEY), 0)  SALE_MONEY,NVL(SUM(SALE_MONEY-ACCOUNTS_RECEIVABLEOUT-CROSS_COMPANY_INCOME-CROSS_COMPANY_PAY) , 0) AMOUNT,NVL(SUM(PAYMENT_MONEY), 0) PAYMENT_MONEY, NVL(SUM(ACCOUNTS_RECEIVABLEIN), 0) " +
                "ACCOUNTS_RECEIVABLEIN, SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT, SUM(YEARCARD) YEARCARD, " +
                "SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT, SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD) BUY_YEARCARD, " +
                "SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE FROM RCL_DAILYSALESREPORT A, " +
                "MAS_PARTY_ROLE C, MAS_PARTY D WHERE 1 = 1 AND C.ID = A.COMPANY_ID AND C.PARTY_ID = D.ID AND " +
                "A.COMPANY_ID = :merchantId AND A.DATE_OF_STATISTICS >= :reconcileBeginDate AND A.DATE_OF_STATISTICS " +
                "< :reconcileEndDate GROUP BY A.COMPANY_ID, D.NAME, A.DEPT_ID order by DEPT_ID";
    }

    /**
     * 委托定损成本明细sql
     *
     * @return
     */
    private String getLossAssessmentCostString() {
        return "SELECT V.ID,\n" +
                "       TO_CHAR(TRUNC(V.CREATE_DATE), 'yyyy-MM-dd') CREATE_DATE,\n" +
                "       I.PRODUCT_ID,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = V.DEPT_ID) DEPT_NAME,\n" +
                "       I.PRICE_TOTAL,\n" +
                "       (SELECT NAME\n" +
                "          FROM MAS_PARTY\n" +
                "         WHERE ID = (SELECT P.PARTY_ID\n" +
                "                       FROM MAS_PARTY_ROLE P\n" +
                "                      WHERE 1 = 1\n" +
                "                        AND P.ID = V.COMPANY_ID)) COMPANY_NAME,\n" +
                "       (SELECT NAME\n" +
                "          FROM MAS_PARTY\n" +
                "         WHERE ID = (SELECT P.PARTY_ID\n" +
                "                       FROM MAS_PARTY_ROLE P\n" +
                "                      WHERE 1 = 1\n" +
                "                        AND P.ID = V.PARTNER_ID)) PARTNER_NAME,\n" +
                "       NVL(I.TAX_COST_PRICE * I.PRODUCT_QTY, 0) TAX_COST_PRICE,\n" +
                "       I.LOSS_QUOTATION_FEE\n" +
                "  FROM ACC_VOUCHER v\n" +
                "  LEFT JOIN ACC_VOUCHER_ITEM I\n" +
                "  LEFT JOIN ACC_VOUCHER_EXTENSION C\n" +
                "    ON I.ID = C.ID ON V.ID = I.VOUCHER_ID\n" +
                " WHERE V.CLASSIFICATION = '09'  AND V.COMPANY_ID = :merchantId  " +
                "   AND V.CREATE_DATE >=:reconcileBeginDate AND V.CREATE_DATE < :reconcileEndDate ORDER BY I.ID";
    }

    /**
     * 应收账款外Sql
     *
     * @return
     */
    private String getAccountSql() {
        return "SELECT NVL(SUM(AMOUNT), 0)                    AMOUNT, " +
                "       DEPT_ID, " +
                "       DEPT_NAME, " +
                "       COMPANY_ID, " +
                "       COMPANY_NAME, " +
                "       CUSTOMER_ID, " +
                "       CUSTOMER_NAME, " +
                "       TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS " +
                "FROM (SELECT B.AMOUNT, " +
                "             C.DEPT_ID, " +
                "             (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME, " +
                "             C.COMPANY_ID, " +
                "             (SELECT C.NAME " +
                "              FROM MAS_PARTY C, " +
                "                   MAS_PARTY_ROLE D " +
                "              WHERE C.ID = D.PARTY_ID " +
                "                AND D.ID = C.COMPANY_ID)                          COMPANY_NAME, " +
                "             B.CUSTOMER_ID, " +
                "             (SELECT C.NAME " +
                "              FROM MAS_PARTY C, " +
                "                   MAS_PARTY_ROLE D " +
                "              WHERE C.ID = D.PARTY_ID " +
                "                AND D.ID = B.CUSTOMER_ID)                         CUSTOMER_NAME, " +
                "             C.DATE_APPROVE " +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B, " +
                "           RETAIL_ORDER C " +
                "      WHERE B.RETAIL_ORDER_ID = C.ID " +
                "        AND C.CLAIM_TYPE IS NULL " +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09') " +
                "        and b.CUSTOMER_ID not in ('C0005998', '02532856', '02533043')" +
                "        and (( (not EXISTS(SELECT 1 \n" +
                "                                                                                 FROM MAS_PARTY_ROLE R \n" +
                "                                                                                 WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID \n" +
                "                                                                                                     FROM MAS_PARTY_ROLE MPR \n" +
                "                                                                                                     WHERE MPR.ID = B.CUSTOMER_ID) \n" +
                "                                                                                   and R.ROLE_ID = 'Company') or \n" +
                "                                                                      ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID) \n" +
                "                                                                          = \n" +
                "                                                                       (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID)) or \n" +
                "                                                                      C.ORDER_TYPE <> \n" +
                "                                                                      'replacement'and B.CUSTOMER_ID not IN ('K1001', 'K1003')) ) or \n" +
                "                              (B.CUSTOMER_ID IN ('K1001', 'K1003') AND C.ORDER_TYPE = 'replacement' and \n" +
                "                               ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID) = (SELECT t.Party_Id \n" +
                "                                                                                                       FROM MAS_PARTY_ROLE t \n" +
                "                                                                                                       WHERE T.ID = B.CUSTOMER_ID) or \n" +
                "                                (not EXISTS(SELECT 1 \n" +
                "                                            FROM MAS_PARTY_ROLE R \n" +
                "                                            WHERE R.PARTY_ID = \n" +
                "                                                  (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID) \n" +
                "                                              and R.ROLE_ID = 'Company')))))" +
                "      UNION ALL " +
                "      SELECT B.AMOUNT, " +
                "             C.DEPT_ID, " +
                "             (SELECT NAME " +
                "              FROM MAS_DEPT A " +
                "              WHERE A.ID = C.DEPT_ID)     DEPT_NAME, " +
                "             C.COMPANY_ID, " +
                "             (SELECT C.NAME " +
                "              FROM MAS_PARTY C, " +
                "                   MAS_PARTY_ROLE D " +
                "              WHERE C.ID = D.PARTY_ID " +
                "                AND D.ID = C.COMPANY_ID)  COMPANY_NAME, " +
                "             B.CUSTOMER_ID, " +
                "             (SELECT C.NAME " +
                "              FROM MAS_PARTY C, " +
                "                   MAS_PARTY_ROLE D " +
                "              WHERE C.ID = D.PARTY_ID " +
                "                AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME, " +
                "             C.DATE_APPROVE " +
                "      FROM ADVANCE_PAYMENT_METHOD B, " +
                "           ADVANCE_PAYMENT C " +
                "      WHERE B.payment_id = C.ID " +
                "        AND C.STATE = 'approve' " +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09') " +
                "        AND B.CUSTOMER_ID <> 'C0005998'           AND (((SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = C.COMPANY_ID) =\n" +
                "                      (SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = B.CUSTOMER_ID)) OR NOT EXISTS(SELECT 1\n" +
                "                                                                  FROM MAS_PARTY_ROLE R\n" +
                "                                                                  WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                      FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                      WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                    and R.ROLE_ID = 'Company'\n" +
                "                  ))) " +
                "WHERE 1 = 1 " +
                "  AND COMPANY_ID = :merchantId " +
                "  AND DATE_APPROVE >= :reconcileBeginDate " +
                "  AND DATE_APPROVE < :reconcileEndDate " +
                "GROUP BY DEPT_ID, DEPT_NAME, COMPANY_ID, COMPANY_NAME, CUSTOMER_ID, CUSTOMER_NAME, " +
                "         TO_CHAR(DATE_APPROVE, " +
                "                 'yyyy-MM-dd') " +
                "order BY TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd')";
    }


    /**
     * 获取成本Sql
     *
     * @return
     */
    private String getSql() {
        return "SELECT * FROM (SELECT T.ID AS     ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME, I.ID I_ID," +
                " T.ORIGIN," +
                "            E.ORIGIN_BUSINESS_TYPE  ORIGIN_BUSINESS_TYPE, " +
                "  T.PARTNER_ID, " +
                "       E.COMPANY_NAME   COMPANY_NAME, " +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE, " +
                "       I.PRICE_TOTAL                 AS                                           PRICE_TOTAL, " +
                "       I.SUPPLY_CHAIN_MANAGEMENT_FEE AS                                           SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "       I.PLATFORM_MANAGEMENT_FEE     AS                                           PLATFORM_MANAGEMENT_FEE, " +
                "       I.PRODUCT_ID                  AS                                           PRODUCT_ID, " +
                "       E.PRODUCT_NAME                AS                                           PRODUCT_NAME, " +
                "       E.SERVE_TYPE_NAME  SERVE_TYPE_NAME, " +
                "       I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "       DECODE(T.INVENTORY_MODE, 'IM_OWN', '自有', 'IM_CONSIGN', '寄售') INVENTORY_MODE," +
                "       I.PRICE_UNIT                  AS                                           PRICE_UNIT, " +
                "       E.MOBILE,E.CAR_LICENSE " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "         LEFT JOIN  ACC_VOUCHER_EXTENSION E ON I.ID=E.ID  " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_OWN' AND I.PRICE_TOTAL <>0  AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465'  " +
                "UNION ALL " +
                "SELECT T.ID   AS      ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME, I.ID I_ID, " +
                " T.ORIGIN," +
                "            E.ORIGIN_BUSINESS_TYPE  ORIGIN_BUSINESS_TYPE, " +
                " T.PARTNER_ID, " +
                "       E.COMPANY_NAME COMPANY_NAME, " +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE, " +
                "       I.PRICE_TOTAL                 AS                                           PRICE_TOTAL, " +
                "       I.SUPPLY_CHAIN_MANAGEMENT_FEE AS                                           SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "       I.PLATFORM_MANAGEMENT_FEE     AS                                           PLATFORM_MANAGEMENT_FEE, " +
                "       I.PRODUCT_ID                  AS                                           PRODUCT_ID, " +
                "       E.PRODUCT_NAME                AS                                           PRODUCT_NAME, " +
                "       E.SERVE_TYPE_NAME        SERVE_TYPE_NAME, " +
                "       I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "       DECODE(T.INVENTORY_MODE, 'IM_OWN', '自有', 'IM_CONSIGN', '寄售') INVENTORY_MODE," +
                "       I.PRICE_UNIT                  AS                                           PRICE_UNIT ," +
                "       E.MOBILE,E.CAR_LICENSE  " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "         LEFT JOIN  ACC_VOUCHER_EXTENSION E ON I.ID=E.ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_CONSIGN' AND I.PRICE_TOTAL <>0 AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465'  ) ORDER BY I_ID ";
    }

    /**
     * 获取成本计数Sql
     *
     * @return
     */
    private String getCountSql() {
        return "SELECT * FROM (SELECT T.ID AS ID  " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_OWN' AND I.PRICE_TOTAL <>0  AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465'  " +
                "UNION ALL " +
                "SELECT T.ID   AS      ID " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_CONSIGN' AND I.PRICE_TOTAL <>0 AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465' ) ";
    }


    /**
     * 获取经销成本Sql
     *
     * @return
     */
    private String getOwnSql() {
        return "SELECT * FROM (SELECT T.ID AS     ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME, I.ID I_ID," +
                " T.ORIGIN," +
                "            E.ORIGIN_BUSINESS_TYPE  ORIGIN_BUSINESS_TYPE, " +
                "  T.PARTNER_ID, " +
                "       E.COMPANY_NAME   COMPANY_NAME, " +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE, " +
                "       I.PRICE_TOTAL                 AS                                           PRICE_TOTAL, " +
                "       I.SUPPLY_CHAIN_MANAGEMENT_FEE AS                                           SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "       I.PLATFORM_MANAGEMENT_FEE     AS                                           PLATFORM_MANAGEMENT_FEE, " +
                "       I.PRODUCT_ID                  AS                                           PRODUCT_ID, " +
                "       E.PRODUCT_NAME                AS                                           PRODUCT_NAME, " +
                "       E.SERVE_TYPE_NAME  SERVE_TYPE_NAME, " +
                "       I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "       DECODE(T.INVENTORY_MODE, 'IM_OWN', '自有', 'IM_CONSIGN', '寄售') INVENTORY_MODE," +
                "       I.PRICE_UNIT                  AS                                           PRICE_UNIT, " +
                "       E.MOBILE,E.CAR_LICENSE " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "         LEFT JOIN  ACC_VOUCHER_EXTENSION E ON I.ID=E.ID  " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_OWN' AND I.PRICE_TOTAL <>0  AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465'  ) ORDER BY I_ID ";
    }

    /**
     * 获取供应商账单成本Sql
     *
     * @return
     */
    private String getInvoiceOwnSql() {
        return " SELECT A.ID, TO_CHAR(A.CREATE_DATE, 'YYYY-MM-DD') AS CREATE_DATE,\n" +
                "       (CASE\n" +
                "         WHEN A.INVENTORY_MODE = 'IM_OWN' THEN '经销'\n" +
                "         WHEN A.INVENTORY_MODE = 'IM_CONSIGN' THEN '代销' ELSE\n" +
                "          A.INVENTORY_MODE\n" +
                "       END) INVENTORY_MODE, A.PARTNER_ID, B.PRODUCT_ID, p.name PRODUCT_NAME,\n" +
                "       (SELECT FC.CAR_LICENSE FROM FN_CUSTOMER_EXPAND FC\n" +
                "          WHERE A.CUSTOMER_EXPAND_ID = FC.ID) CUSTOMER_EXPAND_ID,\n" +
                "       B.PRICE_UNIT, B.QTY, B.PRICE_SUBTOTAL PRICE_SUBTOTAL, B.DISCOUNT,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "       (SELECT S.NAME FROM SOE_BUSINESS_TYPE S\n" +
                "          WHERE S.ID = A.SOURCE_BUSINESS_TYPE) SOURCE_BUSINESS_TYPE,\n" +
                "       A.SOURCE, (SELECT ST.NAME FROM MAS_SERVE_CATEGORY SC, MAS_SERVE_TYPE ST\n" +
                "          where SC.CLASSIFY_ID = p.classify_id and SC.SERVE_TYPE_ID = ST.ID\n" +
                "            and rownum = 1) SERVE_TYPE_NAME,a.origin,(SELECT S.NAME FROM SOE_BUSINESS_TYPE S " +
                "       WHERE S.ID = a.origin_business_type) origin_business_type\n" +
                "FROM ACC_INVOICE a, ACC_INVOICE_ITEM b, MAS_PRODUCT p\n" +
                "   where a.id = b.invoice_id\n" +
                "   and A.PARTNER_ID not in ('004465', 'A00000')\n" +
                "   AND A.ORIGIN_BUSINESS_TYPE <> 'PriceChange'\n" +
                "   and B.PRODUCT_ID = P.ID\n" +
                "   and 'Supplier' =\n" +
                "       (SELECT r.ROLE_ID FROM MAS_PARTY_ROLE r where a.PARTNER_ID = r.id)" +
                "   AND a.inventory_mode=:inventoryMode and A.PARTNER_ID not in('004465','A00000')\n" +
                "   AND A.COMPANY_ID = :merchantId AND A.CREATE_DATE >= :reconcileBeginDate " +
                "  AND A.CREATE_DATE < :reconcileEndDate";

    }

    /**
     * 获取经销成本计数Sql
     *
     * @return
     */
    private String getCountOwnSql() {
        return "SELECT * FROM (SELECT T.ID AS ID  " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_OWN' AND I.PRICE_TOTAL <>0  AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465' ) ";
    }

    /**
     * 获取经销成本计数Sql
     *
     * @return
     */
    private String getCountInvoiceOwnSql() {
        return "SELECT  A.ID,B.PRICE_UNIT, B.QTY,  B.PRICE_SUBTOTAL PRICE_SUBTOTAL,B.DISCOUNT\n" +
                "FROM ACC_INVOICE a, ACC_INVOICE_ITEM b, MAS_PRODUCT p\n" +
                "   where a.id = b.invoice_id\n" +
                "   and A.PARTNER_ID not in ('004465', 'A00000')\n" +
                "   and B.PRODUCT_ID = P.ID\n" +
                "   AND A.ORIGIN_BUSINESS_TYPE <> 'PriceChange'\n" +
                "   and 'Supplier' = (SELECT r.ROLE_ID FROM MAS_PARTY_ROLE r where a.PARTNER_ID = r.id)" +
                "   AND a.inventory_mode=:inventoryMode and A.PARTNER_ID not in('004465','A00000')\n" +
                "   AND A.COMPANY_ID = :merchantId AND A.CREATE_DATE >= :reconcileBeginDate " +
                "  AND A.CREATE_DATE < :reconcileEndDate";

    }


    /**
     * 获取代销成本Sql
     *
     * @return
     */
    private String getConsignSql() {
        return "SELECT * FROM (SELECT T.ID   AS      ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME, I.ID I_ID, " +
                " T.ORIGIN," +
                "            E.ORIGIN_BUSINESS_TYPE  ORIGIN_BUSINESS_TYPE, " +
                " T.PARTNER_ID, " +
                "       E.COMPANY_NAME COMPANY_NAME, " +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE, " +
                "       I.PRICE_TOTAL                 AS                                           PRICE_TOTAL, " +
                "       I.SUPPLY_CHAIN_MANAGEMENT_FEE AS                                           SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "       I.PLATFORM_MANAGEMENT_FEE     AS                                           PLATFORM_MANAGEMENT_FEE, " +
                "       I.PRODUCT_ID                  AS                                           PRODUCT_ID, " +
                "       E.PRODUCT_NAME                AS                                           PRODUCT_NAME, " +
                "       E.SERVE_TYPE_NAME        SERVE_TYPE_NAME, " +
                "       I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "       DECODE(T.INVENTORY_MODE, 'IM_OWN', '自有', 'IM_CONSIGN', '寄售') INVENTORY_MODE," +
                "       I.PRICE_UNIT                  AS                                           PRICE_UNIT ," +
                "       E.MOBILE,E.CAR_LICENSE  " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "         LEFT JOIN  ACC_VOUCHER_EXTENSION E ON I.ID=E.ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_CONSIGN' AND I.PRICE_TOTAL <>0 AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465'  ) ORDER BY I_ID ";
    }

    /**
     * 获取代销成本计数Sql
     *
     * @return
     */
    private String getCountConsignSql() {
        return "SELECT * FROM (SELECT T.ID   AS      ID " +
                "FROM ACC_VOUCHER T " +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "  AND T.CREATE_DATE < :reconcileEndDate " +
                "  AND T.COMPANY_ID = :merchantId " +
                "  AND T.VOUCHER_TYPE = 'SUPPLIER_CONSUME' " +
                "  AND (T.SCENE_ID <> 'PO002' OR T.SCENE_ID IS NULL)  " +
                "  AND T.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss' " +
                "  AND T.INVENTORY_MODE = 'IM_CONSIGN' AND I.PRICE_TOTAL <>0 AND T.PARTNER_ID <>'A00000' AND T.PARTNER_ID <>'004465' ) ";
    }


    /**
     * 获取供应链管理费Sql
     *
     * @return
     */
    private String getManagementFeSql() {
        return "select * " +
                "from ( " +
                "         select T.ID                                            AS                         ID, " +
                "                I.ID                                                                       I_ID, " +
                "                T.ORIGIN                                                                      ORIGIN, " +
                "                E.ORIGIN_BUSINESS_TYPE             ORIGIN_BUSINESS_TYPE," +
                "                (SELECT NAME FROM soe_business_type WHERE ID = T.BUSINESS_TYPE)            BUSINESS_NAME, " +
                "                T.PARTNER_ID, " +
                "                E.PARTNER_NAME  PARTNER_NAME, " +
                "                T.COMPANY_ID, " +
                "                E.COMPANY_NAME  COMPANY_NAME, " +
                "                E.SERVE_TYPE_NAME  SERVE_TYPE_NAME, " +
                "                TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS                         CREATE_DATE, " +
                "                I.PRICE_TOTAL                                   AS                         PRICE_TOTAL, " +
                "                I.SUPPLY_CHAIN_MANAGEMENT_FEE                   AS                         SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                I.PLATFORM_MANAGEMENT_FEE                       AS                         PLATFORM_MANAGEMENT_FEE, " +
                "                I.PRODUCT_ID                                    AS                         PRODUCT_ID, " +
                "                E.PRODUCT_NAME                                   AS                         PRODUCT_NAME, " +
                "                I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "                I.PRICE_UNIT                                    AS                         PRICE_UNIT," +
                "                E.MOBILE, E.CAR_LICENSE  " +
                "         FROM ACC_VOUCHER T " +
                "                  LEFT JOIN ACC_VOUCHER_ITEM I " +
                "                            ON T.ID = I.VOUCHER_ID " +
                "                  LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID=E.ID " +
                "         WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "           AND T.VOUCHER_TYPE " +
                "             = 'SUPPLIER_PURCHASE' " +
                "           AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "           AND T.INVENTORY_MODE = 'IM_OWN' " +
                "           and (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null) " +
                "           AND I.PRICE_TOTAL <> 0 " +
                " " +
                "         union ALL " +
                "         select T.ID                                            AS                         ID, " +
                "                I.ID                                                                       I_ID, " +
                "                T.ORIGIN                                                                      ORIGIN, " +
                "                E.ORIGIN_BUSINESS_TYPE             ORIGIN_BUSINESS_TYPE," +
                "                (SELECT NAME FROM soe_business_type WHERE ID = T.BUSINESS_TYPE)            BUSINESS_NAME, " +
                "                T.PARTNER_ID, " +
                "                E.PARTNER_NAME PARTNER_NAME, " +
                "                T.COMPANY_ID, " +
                "                E.COMPANY_NAME COMPANY_NAME, " +
                "                E.SERVE_TYPE_NAME SERVE_TYPE_NAME, " +
                "                TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS                         CREATE_DATE, " +
                "                I.PRICE_TOTAL                                   AS                         PRICE_TOTAL, " +
                "                I.SUPPLY_CHAIN_MANAGEMENT_FEE                   AS                         SUPPLY_CHAIN_MANAGEMENT_FEE, " +
                "                I.PLATFORM_MANAGEMENT_FEE                       AS                         PLATFORM_MANAGEMENT_FEE, " +
                "                I.PRODUCT_ID                                    AS                         PRODUCT_ID, " +
                "                E.PRODUCT_NAME                                  AS                         PRODUCT_NAME, " +
                "                I.PRODUCT_QTY                 AS                                           PRODUCT_QTY, " +
                "                I.PRICE_UNIT                                    AS                         PRICE_UNIT , " +
                "                E.MOBILE, E.CAR_LICENSE " +
                "         FROM ACC_VOUCHER T " +
                "                  LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "                  LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID=E.ID " +
                "         WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "           AND T.VOUCHER_TYPE " +
                "             = 'SUPPLIER_CONSUME' " +
                "           AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "           and (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null) " +
                "           AND T.INVENTORY_MODE = 'IM_CONSIGN' " +
                "           AND I.PRICE_TOTAL <> 0" +
                "" +
                "           UNION ALL " +
                "                     SELECT T.ID                                            AS                         ID,\n" +
                "                                I.ID                                                                       I_ID,\n" +
                "                                T.ORIGIN                                                                      ORIGIN,\n" +
                "                                E.ORIGIN_BUSINESS_TYPE             ORIGIN_BUSINESS_TYPE,\n" +
                "                                (SELECT NAME FROM soe_business_type WHERE ID = T.BUSINESS_TYPE)            BUSINESS_NAME,\n" +
                "                                T.PARTNER_ID,\n" +
                "                                E.PARTNER_NAME PARTNER_NAME,\n" +
                "                                T.COMPANY_ID,\n" +
                "                                E.COMPANY_NAME COMPANY_NAME,\n" +
                "                                E.SERVE_TYPE_NAME SERVE_TYPE_NAME,\n" +
                "                                TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS                         CREATE_DATE,\n" +
                "                                I.PRICE_TOTAL                                   AS                         PRICE_TOTAL,\n" +
                "                                I.SUPPLY_CHAIN_MANAGEMENT_FEE                   AS                         SUPPLY_CHAIN_MANAGEMENT_FEE,\n" +
                "                                I.PLATFORM_MANAGEMENT_FEE                       AS                         PLATFORM_MANAGEMENT_FEE,\n" +
                "                                I.PRODUCT_ID                                    AS                         PRODUCT_ID,\n" +
                "                                E.PRODUCT_NAME                                  AS                         PRODUCT_NAME,\n" +
                "                                I.PRODUCT_QTY                 AS                                           PRODUCT_QTY,\n" +
                "                                I.PRICE_UNIT                                    AS                         PRICE_UNIT ,\n" +
                "                                E.MOBILE, E.CAR_LICENSE\n" +
                "                         FROM ACC_VOUCHER T\n" +
                "                                  LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "                                  LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID=E.ID\n" +
                "                                  LEFT JOIN MAS_STORE D ON T.DEPT_ID = D.ID,V_SERVE_TYPE S\n" +
                "               WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "                     AND T.CREATE_DATE < :reconcileEndDate " +
                "                     AND T.COMPANY_ID = :merchantId " +
                "                     AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE'\n" +
                "                     AND T.SCENE_ID = 'SR004'\n" +
                "                     AND T.INVENTORY_MODE = 'IM_OWN'\n" +
                "                     AND I.PRICE_TOTAL <> 0\n" +
                "                     AND D.STORE_TYPE_NAME = '001171'\n" +
                "                     AND PARTNER_ID = 'A1001'\n" +
                "                     AND S.PRODUCTID = I.PRODUCT_ID\n" +
                "                     AND S.SERVE_TYPE = '007'" +
                ") ORDER BY I_ID";
    }

    /**
     * 获取供应链管理费计数Sql
     *
     * @return
     */
    private String getManagementFeCountSql() {
        return "select * " +
                "from ( " +
                "         select T.ID  AS     ID " +

                "         FROM ACC_VOUCHER T " +
                "                  LEFT JOIN ACC_VOUCHER_ITEM I " +
                "                            ON T.ID = I.VOUCHER_ID " +
                "         WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "           AND T.VOUCHER_TYPE " +
                "             = 'SUPPLIER_PURCHASE' " +
                "           AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "           AND T.INVENTORY_MODE = 'IM_OWN' " +
                "           and (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null) " +
                "           AND I.PRICE_TOTAL <> 0 " +
                " " +
                "         union ALL " +
                "         select T.ID AS ID " +
                "         FROM ACC_VOUCHER T " +
                "                  LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "         WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate " +
                "               AND T.COMPANY_ID = :merchantId " +
                "           AND T.VOUCHER_TYPE " +
                "             = 'SUPPLIER_CONSUME' " +
                "           AND (T.SCENE_ID <> 'PO002' AND T.SCENE_ID <>'SR004' OR T.SCENE_ID IS NULL) " +
                "           and (CLASSIFICATION not in ('05', '06') or CLASSIFICATION is null) " +
                "           AND T.INVENTORY_MODE = 'IM_CONSIGN' " +
                "           AND I.PRICE_TOTAL <> 0" +
                "" +
                "         union ALL " +
                "         select T.ID AS ID " +
                "         FROM ACC_VOUCHER T " +
                "                  LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID " +
                "                  LEFT JOIN MAS_STORE D ON T.DEPT_ID = D.ID,V_SERVE_TYPE S\n" +
                "               WHERE T.CREATE_DATE >= :reconcileBeginDate " +
                "                     AND T.CREATE_DATE < :reconcileEndDate " +
                "                     AND T.COMPANY_ID = :merchantId " +
                "                     AND T.VOUCHER_TYPE = 'SUPPLIER_PURCHASE'\n" +
                "                     AND T.SCENE_ID = 'SR004'\n" +
                "                     AND T.INVENTORY_MODE = 'IM_OWN'\n" +
                "                     AND I.PRICE_TOTAL <> 0\n" +
                "                     AND D.STORE_TYPE_NAME = '001171'\n" +
                "                     AND PARTNER_ID = 'A1001'\n" +
                "                     AND S.PRODUCTID = I.PRODUCT_ID\n" +
                "                     AND S.SERVE_TYPE = '007'" +
                "" +
                ")";
    }

    /**
     * 获取会员跨公司消费-支出SQL
     *
     * @return
     */
    private String getCrossCompanyPaySql() {
        return "SELECT A.COMPANY_ID                                                              GSID,\n" +
                "       '[' || A.COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                      FROM MAS_PARTY A,\n" +
                "                                           MAS_PARTY_ROLE P\n" +
                "                                      WHERE A.ID = P.PARTY_ID\n" +
                "                                        AND P.ID = A.COMPANY_ID)                 COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.COMPANY_ID)                                               GSMC,\n" +
                "       A.PARTNER_COMPANY_ID\n" +
                "                                                                                  SSGSID,\n" +
                "       '[' || A.PARTNER_COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                              FROM MAS_PARTY A,\n" +
                "                                                   MAS_PARTY_ROLE P\n" +
                "                                              WHERE A.ID = P.PARTY_ID\n" +
                "                                                AND P.ID = A.PARTNER_COMPANY_ID) PARTNER_COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.PARTNER_COMPANY_ID)                                       SSGSMC,\n" +
                "       TO_CHAR(A.STATISTIC_DATE,\n" +
                "               'yyyy-MM-dd')                                                     TJRQ,\n" +
                "       A.USE_SAVINGS ,\n" +
                "       A.GIVE_SAVINGS ,\n" +
                "       A.AMOUNT_USED_HB ,\n" +
                "       A.AMOUNT_USED_BB \n" +
                "        ,\n" +
                "       '支出' TYPE,\n" +
                "       A.SETTLEMENT_AMOUNT_HB ,\n" +
                "       A.SETTLEMENT_AMOUNT_BB ,\n" +
                "       A.amount_used_kq \n" +
                "        ,\n" +
                "       A.PARTNER_DEPT_ID                                                         SSBMID,\n" +
                "       (SELECT '[' || A.PARTNER_DEPT_ID || ']' || B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.PARTNER_DEPT_ID)                                          PARTNER_DEPT,\n" +
                "       A.DEPT_ID                                                                 BMID,\n" +
                "       (SELECT B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.DEPT_ID)                                                  BMMC,\n" +
                "       '[' || A.DEPT_ID || ']' || (SELECT B.NAME\n" +
                "                                   FROM MAS_DEPT B\n" +
                "                                   WHERE B.ID\n" +
                "                                             = A.DEPT_ID)                        DEPT," +
                "       A.SETTLEMENT_AMOUNT ,\n" +
                "       settlement_amount_kq ,\n" +
                "       A.platform_management_fee,\n" +
                "       (select '[' || C.ID || ']' || C.NAME from mas_access_provider C,MAS_PARTY_ROLE D where D.ID = a.PARTNER_COMPANY_ID AND d.ACCESS_PROVIDER_ID = C.ID)PARTNER_ACCESS_PROVIDER,\n" +
                "       (select '[' || C.ID || ']' || C.NAME from mas_access_provider C,MAS_PARTY_ROLE D where D.ID = a.COMPANY_ID AND d.ACCESS_PROVIDER_ID = C.ID)ACCESS_PROVIDER                                                       \n" +
                " FROM VIP_UNION_REPORT A\n" +
                " WHERE 1 = 1\n" +
                "  AND A.STATISTIC_DATE >= :reconcileBeginDate\n" +
                "  AND A.STATISTIC_DATE < :reconcileEndDate\n" +
                "  AND A.COMPANY_ID = :merchantId " +
                "   and TYPE = 'out'\n" +
                "ORDER BY A.STATISTIC_DATE";
    }

    /**
     * 获取会员跨公司消费-收入SQL
     *
     * @return
     */
    private String getCrossCompanyIncomeSql() {
        return "SELECT A.COMPANY_ID                                                              GSID,\n" +
                "       '[' || A.COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                      FROM MAS_PARTY A,\n" +
                "                                           MAS_PARTY_ROLE P\n" +
                "                                      WHERE A.ID = P.PARTY_ID\n" +
                "                                        AND P.ID = A.COMPANY_ID)                 COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.COMPANY_ID)                                               GSMC,\n" +
                "       A.PARTNER_COMPANY_ID\n" +
                "                                                                                  SSGSID,\n" +
                "       '[' || A.PARTNER_COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                              FROM MAS_PARTY A,\n" +
                "                                                   MAS_PARTY_ROLE P\n" +
                "                                              WHERE A.ID = P.PARTY_ID\n" +
                "                                                AND P.ID = A.PARTNER_COMPANY_ID) PARTNER_COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.PARTNER_COMPANY_ID)                                       SSGSMC,\n" +
                "       TO_CHAR(A.STATISTIC_DATE,\n" +
                "               'yyyy-MM-dd')                                                     TJRQ,\n" +
                "       A.USE_SAVINGS ,\n" +
                "       A.GIVE_SAVINGS ,\n" +
                "       A.AMOUNT_USED_HB ,\n" +
                "       A.AMOUNT_USED_BB \n" +
                "        ,\n" +
                "       '收入' TYPE,\n" +
                "       A.SETTLEMENT_AMOUNT_HB ,\n" +
                "       A.SETTLEMENT_AMOUNT_BB ,\n" +
                "       A.amount_used_kq \n" +
                "        ,\n" +
                "       A.PARTNER_DEPT_ID                                                         SSBMID,\n" +
                "       (SELECT '[' || A.PARTNER_DEPT_ID || ']' || B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.PARTNER_DEPT_ID)                                          PARTNER_DEPT,\n" +
                "       A.DEPT_ID                                                                 BMID,\n" +
                "       (SELECT B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.DEPT_ID)                                                  BMMC,\n" +
                "       '[' || A.DEPT_ID || ']' || (SELECT B.NAME\n" +
                "                                   FROM MAS_DEPT B\n" +
                "                                   WHERE B.ID\n" +
                "                                             = A.DEPT_ID)                        DEPT," +
                "       A.SETTLEMENT_AMOUNT ,\n" +
                "       settlement_amount_kq ,\n" +
                "       A.platform_management_fee,\n" +
                "      (select '[' || d.ID || ']' || d.NAME from yyesb.mas_access_provider d left outer join yyesb.s_access_company b\n" +
                "             on d.id = b.access_id left outer join yyesb.mas_party_role C  on b.company_id = c.id where c.id = a.PARTNER_COMPANY_ID) PARTNER_ACCESS_PROVIDER,\n" +
                "       (select '[' || C.ID || ']' || C.NAME from mas_access_provider C,MAS_PARTY_ROLE D where D.ID = a.COMPANY_ID AND d.ACCESS_PROVIDER_ID = C.ID)ACCESS_PROVIDER                                                       \n" +
                " FROM VIP_UNION_REPORT A\n" +
                " WHERE 1 = 1\n" +
                "  AND A.STATISTIC_DATE >= :reconcileBeginDate\n" +
                "  AND A.STATISTIC_DATE < :reconcileEndDate\n" +
                "  AND A.COMPANY_ID = :merchantId " +
                "   and A.type = 'in'\n" +
                "ORDER BY A.STATISTIC_DATE,A.PARTNER_DEPT_ID,DEPT_ID";
    }

    /**
     * 平台服务费减免明细Sql
     *
     * @return
     */
    private String getFeeReducedDetailSql() {
        return "SELECT E.PRODUCT_CLASSIFY,\n" +
                "       TO_CHAR(:reconcileBeginDate ,'yyyy-MM-dd')                    START_DATE,\n" +
                "       TO_CHAR(:reconcileEndDate,'yyyy-MM-dd')                      END_DATE,\n" +
                "       T.COMPANY_ID,\n" +
                "       E.COMPANY_NAME,\n" +
                "       SUM(I.PLATFORM_SERVICE_FEE_REDUCED) FEE,\n" +
                "       SUM(I.PLATFORM_MANAGEMENT_FEE)      PLATFORM_MANAGEMENT_FEE\n" +
                "FROM ACC_VOUCHER T\n" +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "         LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID = E.ID\n" +
                "WHERE T.COMPANY_ID = :merchantId  AND VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND I.PLATFORM_SERVICE_FEE_REDUCED <> 0\n" +
                "  AND T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate " +
                "GROUP BY E.PRODUCT_CLASSIFY, T.COMPANY_ID, E.COMPANY_NAME";
    }


    /**
     * 平台服务费减免项目明细Sql
     *
     * @return
     */
    private String getFeeReducedItemSql() {
        return "SELECT T.ID V_ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME,\n" +
                "       T.ORIGIN ID,E.ORIGIN_BUSINESS_TYPE,\n" +
                "       T.COMPANY_ID,\n" +
                "       E.COMPANY_NAME,\n" +
                "       E.PARTNER_NAME,\n" +
                "       E.PRODUCT_CLASSIFY,\n" +
                "       I.PRODUCT_ID,E.PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_QTY,\n" +
                "       I.PRICE_TOTAL,\n" +
                "       I.DISCOUNT,\n" +
                "       I.PLATFORM_SERVICE_FEE_REDUCED,\n" +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE,\n" +
                "       I.PLATFORM_MANAGEMENT_FEE  " +
                " FROM ACC_VOUCHER T\n" +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "         LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID = E.ID\n" +
                "WHERE T.COMPANY_ID = :merchantId  AND VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND I.PLATFORM_SERVICE_FEE_REDUCED <> 0\n" +
                "  AND E.PRODUCT_CLASSIFY =:PRODUCT_CLASSIFY\n" +
                "  AND T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate ";
    }

    /**
     * 内部销售服务费减免明细Sql
     *
     * @return
     */
    private String getInternalDetailSql() {
        return "SELECT T.ID V_ID,(SELECT NAME FROM soe_business_type WHERE ID= T.BUSINESS_TYPE)BUSINESS_NAME,\n" +
                "       T.ORIGIN ID,E.ORIGIN_BUSINESS_TYPE,\n" +
                "       T.COMPANY_ID,\n" +
                "       E.COMPANY_NAME,\n" +
                "       E.PARTNER_NAME,\n" +
                "       E.PRODUCT_CLASSIFY,\n" +
                "       I.PRODUCT_ID,E.PRODUCT_NAME,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PRODUCT_QTY,\n" +
                "       I.PRICE_TOTAL,\n" +
                "       I.DISCOUNT,\n" +
                "       I.PLATFORM_INTERNAL_REDUCED,\n" +
                "       TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS')                 AS                                           CREATE_DATE,\n" +
                "       I.PLATFORM_MANAGEMENT_FEE  " +
                " FROM ACC_VOUCHER T\n" +
                "         LEFT JOIN ACC_VOUCHER_ITEM I ON T.ID = I.VOUCHER_ID\n" +
                "         LEFT JOIN ACC_VOUCHER_EXTENSION E ON I.ID = E.ID\n" +
                "WHERE T.COMPANY_ID = :merchantId  AND VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "  AND I.PLATFORM_INTERNAL_REDUCED <> 0\n" +
                "  AND T.CREATE_DATE >= :reconcileBeginDate " +
                "               AND T.CREATE_DATE < :reconcileEndDate ";
    }

    /**
     * 获取结果
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @param page
     * @param size
     * @param sb
     * @return
     */
    private List<Map> getResults(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, int page, int size, String sb) {
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("reconcileBeginDate", reconcileBeginDate);
        query.setParameter("reconcileEndDate", reconcileEndDate);
        query.setParameter("merchantId", merchantId);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList;
    }


    /**
     * 获取委托定损成本数据
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> getLossAssessmentCostData(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        //取结果集
        String sb = "SELECT TRUNC(V.CREATE_DATE) CREATE_DATE,\n" +
                "       NVL(I.TAX_COST_PRICE * I.PRODUCT_QTY, 0)+I.LOSS_QUOTATION_FEE TAX_COST_PRICE\n" +
                "   FROM ACC_VOUCHER v\n" +
                "  LEFT JOIN ACC_VOUCHER_ITEM I\n" +
                "  LEFT JOIN ACC_VOUCHER_EXTENSION C\n" +
                "    ON I.ID = C.ID ON V.ID = I.VOUCHER_ID\n" +
                " WHERE V.CLASSIFICATION = '09'\n" +
                "    AND v.COMPANY_ID = :COMPANY_ID AND V.CREATE_DATE  >= :startDate AND V.CREATE_DATE < :endDate";
        return resultPackage(merchantId, reconcileBeginDate, reconcileEndDate, sb);
    }

    /**
     * 获取商户营业额数据
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> getBusinessTurnoverData(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        //取结果集
        String sb = "   SELECT DATE_OF_STATISTICS ,\n" +
                "              DEPT_ID,\n" +
                "              COMPANY_ID,\n" +
                "              SALE_MONEY,\n" +
                "             (SALE_MONEY-CROSS_COMPANY_PAY) INCOME \n" +
                "        FROM RCL_DAILYSALESREPORT U\n" +
                "      WHERE 1 = 1 \n" +
                " AND COMPANY_ID=:COMPANY_ID AND DATE_OF_STATISTICS >= :startDate AND DATE_OF_STATISTICS< :endDate";
        return resultPackage(merchantId, reconcileBeginDate, reconcileEndDate, sb);
    }

    /**
     * 获取商户营业总额数据
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @return
     */
    public List<Map> getSumBusinessTurnoverData(String merchantId, Date reconcileBeginDate, Date reconcileEndDate) {
        //取结果集
        String sb = "   SELECT SUM(SALE_MONEY-ACCOUNTS_RECEIVABLEOUT-CROSS_COMPANY_INCOME-CROSS_COMPANY_PAY)  SALE_MONEY, SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "              SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,SUM(CROSS_COMPANY_INCOME) CROSS_COMPANY_INCOME,SUM(CROSS_COMPANY_PAY) CROSS_COMPANY_PAY, " +
                "              SUM(ACCOUNTS_RECEIVABLEOUT)  ACCOUNTS_RECEIVABLEOUT\n" +
                "        FROM RCL_DAILYSALESREPORT U\n" +
                "      WHERE 1 = 1 \n" +
                " AND COMPANY_ID=:COMPANY_ID AND DATE_OF_STATISTICS >= :startDate AND DATE_OF_STATISTICS< :endDate";
        return resultPackage(merchantId, reconcileBeginDate, reconcileEndDate, sb);
    }


    /**
     * 返回结果封装
     *
     * @param merchantId
     * @param reconcileBeginDate
     * @param reconcileEndDate
     * @param sb
     * @return
     */
    private List<Map> resultPackage(String merchantId, Date reconcileBeginDate, Date reconcileEndDate, String sb) {
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("startDate", reconcileBeginDate);
        query.setParameter("endDate", DateUtil.add(reconcileEndDate, Calendar.DAY_OF_MONTH, 1));
        query.setParameter("COMPANY_ID", merchantId);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList;
    }

    /**
     * 查找平台费用(费用账单)
     *
     * @param id
     * @return
     */
    public List<Map> costPlatform(String id, int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getCostPlatformSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("id", id);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }

    /**
     * 查找预支单
     *
     * @param
     * @return
     */
    public List<Map> businessform(String id,  int page, int size) {
        String sb = "SELECT * FROM ( " +
                "SELECT CB.*, ROWNUM AS rowno FROM ( " +
                getBusinessformSql() +
                " ) CB WHERE ROWNUM <=:end )T_CB WHERE T_CB.rowno >:start";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("id", id);
        query.setParameter("start", (page - 1) * size);
        query.setParameter("end", page * size);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return query.getResultList();
    }


    /**
     * 查找预支单的总页数
     *
     * @return
     */
    public Object businessformCount(String id) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getBusinessformSqlCount() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("id", id);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 货币资金（不含定损代收）
     *
     * @param param
     * @return
     */
    public JSONArray costPlatform1(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT t.name, sum(t.recovery_amount) withholdingAmount,\n" +
                "       sum(t.freeze_amount) currentDeduction, sum(t.amount) amount,\n" +
                "       sum(t.AMOUNTDEDUCTED) AMOUNTDEDUCTED\n" +
                "  FROM (SELECT (SELECT NAME FROM MAS_INCOME_TYPE WHERE a.INCOME_TYPE_ID = ID) name,\n" +
                "                  case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                  sum(a.AMOUNT) * -1\n" +
                "                  else\n" +
                "                   sum(a.AMOUNT) \n" +
                "                end AMOUNT, " +
                "                 case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   sum(a.recovery_amount) * -1\n" +
                "                  else\n" +
                "                   sum(a.recovery_amount)\n" +
                "                end recovery_amount, " +
                "                 case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   sum(a.freeze_amount) * -1\n" +
                "                  else\n" +
                "                   sum(a.freeze_amount)\n" +
                "                end freeze_amount, " +
                "                 case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   sum(a.freeze_amount) * -1\n" +
                "                  else\n" +
                "                   sum(a.freeze_amount)\n" +
                "                end  AMOUNTDEDUCTED\n" +
                "           FROM pur_income_item a\n" +
                "           left join pur_income b\n" +
                "             on a.income_id = b.id\n" +
                "            where b.state <> 'sent'\n");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS != null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE", DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END", DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

        if (param.get("COMPANY_ID") != null) stringBuffer.append(" AND b.company_id  = :COMPANY_ID ");
        if (param.get("DATE_OF_STATISTICS_STATE") != null) {
            try {
                if (Objects.isNull(param.getString("DATE_APPROVE"))) {
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    Date date = formatter.parse(param.getString("DATE_APPROVE"));
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
                }
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    if (null == param.getString("DATE_APPROVE")) {
                        stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    } else {
                        Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                        LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                        if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()) {
                            //获取月末
                            LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                            String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfLastDate", SmonthOfLastDate);
                            stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND b.CREATE_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                        } else {
                            //获取月头
                            LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                            String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfFirstDate", SmonthOfFirstDate);
                            stringBuffer.append(" AND b.CREATE_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                            stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                        }
                    }
                }
            }
        }
        stringBuffer.append(" group by a.INCOME_TYPE_ID,b.PARTNER_ID ) t group by t.name");
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * String --> Date
     *
     * @param endDate
     * @param dateFormat
     * @return
     */
    public Date StringToDate(String endDate, String dateFormat) {
        DateFormat format = new SimpleDateFormat(dateFormat);
        Date eDate = null;
        try {
            eDate = format.parse(endDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return eDate;
    }

    /**
     * 2.0获取费用账单Sql
     *
     * @return
     */
    private String getCostPlatformSql() {
        return "select a.merchant_settlement_id id,\n" +
                "        (SELECT NAME\n" +
                "                             FROM MAS_INCOME_TYPE\n" +
                "                            WHERE b.INCOME_TYPE_ID = ID) name,\n" +
                "       Round(sum(a.AMOUNT),2) amount, Round(sum(a.AMOUNT_DEDUCTED),2) withholdingAmount, Round(sum(a.CURRENT_AMOUNT),2) currentDeduction,\n" +
                "       Round(sum(a.BALANCE_AMOUNT),2) amountDeducted\n" +
                "  from merchant_expense_item a,pur_income_item b\n" +
                " where b.id=a.income_item_id and a.merchant_settlement_id = :id\n" +
                " group by b.INCOME_TYPE_ID, a.merchant_settlement_id order by b.INCOME_TYPE_ID \n";
    }

    /**
     * 2.0获取费用账单Sql
     *
     * @return
     */
    private String getBusinessformSql() {
        return "SELECT b.id, b.capital_balance, b.reason,\n" +
                "       to_char(b.create_date, 'yyyy-mm-dd') create_date,\n" +
                "       (SELECT C.NAME\n" +
                "           FROM MAS_PARTY_ROLE B, MAS_PARTY C\n" +
                "          WHERE b.CREATOR_ID = B.ID\n" +
                "            AND B.PARTY_ID = C.ID) CREATOR_NAME,\n" +
                "       '[' || b.FN_CUSTOMER_ID || '] ' ||\n" +
                "        (SELECT NAME\n" +
                "           FROM MAS_PARTY\n" +
                "          WHERE ID = (SELECT P.PARTY_ID\n" +
                "                        FROM MAS_PARTY_ROLE P\n" +
                "                       WHERE 1 = 1\n" +
                "                         AND P.ID = b.FN_CUSTOMER_ID)) FN_CUSTOMER_NAME,\n" +
                "       to_char(b.approve_date, 'yyyy-mm-dd') approve_date\n" +
                "  FROM merchant_business_item a, fn_business_debit_note b\n" +
                " where a.business_id = b.id\n" +
                "   and a.merchant_settlement_id = :id\n";
    }

    /**
     * 获取费用账单总数Sql
     *
     * @return
     */
    private String getBusinessformSqlCount() {
        return "SELECT count(1)\n" +
                "  FROM merchant_business_item a, fn_business_debit_note b\n" +
                " where a.business_id = b.id\n" +
                "   and a.merchant_settlement_id = :id\n";
    }


    /**
     * 查找平台费用(费用账单)的总页数
     *
     * @return
     */
    public Object costPlatformCount(String id) {
        String sb = "SELECT COUNT(1) COUNT FROM ( " +
                getCountCostPlatformSql() +
                " ) ";
        Query query = entityManager.createNativeQuery(sb);
        query.setParameter("id", id);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        List<Map> resultList = query.getResultList();
        return resultList.get(0).get("COUNT");
    }

    /**
     * 获取费用账单总数Sql
     *
     * @return
     */
    private String getCountCostPlatformSql() {
        return "select count(1) " +
                "  from merchant_expense_item a,pur_income_item b" +
                " where b.id=a.income_item_id and a.merchant_settlement_id = :id" +
                " group by b.INCOME_TYPE_ID, a.merchant_settlement_id ";
    }

    /**
     * 2.0查找平台费用(费用账单)
     *
     * @param id
     * @return
     */
    public List<Map> merchantCostPlatform(String id, String name) {
        String sql = "select b.income_id id, " +
                "  (SELECT NAME FROM MAS_INCOME_TYPE WHERE B.INCOME_TYPE_ID = ID) INCOME_TYPE_NAME,\n" +
                "       (CASE\n" +
                "         WHEN (SELECT ROLE_ID FROM MAS_PARTY_ROLE WHERE ID = c.PARTNER_ID) =\n" +
                "              'Supplier' THEN\n" +
                "         Round( B.AMOUNT * -1,2)\n" +
                "         ELSE\n" +
                "          Round(B.AMOUNT,2)\n" +
                "       END) AMOUNT, Round(a.AMOUNT_DEDUCTED,2) RECOVERY_AMOUNT,\n" +
                "       Round(a.BALANCE_AMOUNT,2) FREEZE_AMOUNT,\n" +
                "       (CASE\n" +
                "         WHEN (SELECT ROLE_ID FROM MAS_PARTY_ROLE WHERE ID = c.PARTNER_ID) =\n" +
                "              'Supplier' THEN\n" +
                "          Round(a.current_amount * -1,2)\n" +
                "         ELSE\n" +
                "          Round(a.current_amount,2)\n" +
                "       END) SETTLEMENT_AMOUNT,\n" +
                "       TO_CHAR(B.START_DATE, 'yyyy-mm-dd') AS START_DATE,\n" +
                "       TO_CHAR(B.END_DATE, 'yyyy-mm-dd') AS END_DATE, B.NOTE, c.ORIGIN, " +
                " (CASE WHEN c.SOURCE_BUSINESS_KEY='Expense' THEN '平台费用'" +
                "        WHEN c.SOURCE_BUSINESS_KEY='SettleIncome' THEN '平台定损费用'" +
                "        WHEN c.SOURCE_BUSINESS_KEY = 'LossQuotation' THEN '定损单'" +
                "        WHEN c.SOURCE_BUSINESS_KEY = 'Voucher' THEN '业务凭证'" +
                "        WHEN c.SOURCE_BUSINESS_KEY='server' THEN '服务'" +
                "        ELSE C.SOURCE_BUSINESS_KEY END) SOURCE_BUSINESS_KEY," +
                "    c.SOURCE,(CASE WHEN c.SOURCE_BUSINESS_TYPE='Expense' THEN '平台费用'" +
                "        WHEN c.SOURCE_BUSINESS_TYPE='SettleExpense' THEN '平台定损'" +
                "        WHEN c.SOURCE_BUSINESS_TYPE = 'SettleIncome' THEN '平台定损费用'" +
                "           WHEN c.SOURCE_BUSINESS_KEY = 'LossQuotation' THEN '定损单'" +
                "        WHEN c.SOURCE_BUSINESS_TYPE='server' THEN '服务'" +
                "        ELSE C.SOURCE_BUSINESS_TYPE END) SOURCE_BUSINESS_TYPE," +
                " (SELECT NAME  FROM MAS_PARTY  WHERE ID = (SELECT P.PARTY_ID FROM " +
                " MAS_PARTY_ROLE P WHERE P.ID = c.CREATOR_ID)) CREATOR," +
                " TO_CHAR(c.CREATE_DATE, 'yyyy-mm-dd') AS CREATE_DATE,c.CAR_LICENSE" +
                " from merchant_expense_item a,pur_income_item b,PUR_INCOME c where b.id=a.income_item_id " +
                " and b.income_id=C.id and   a.merchant_settlement_id ='" + id + "'  and (SELECT NAME FROM MAS_INCOME_TYPE WHERE B.INCOME_TYPE_ID = ID) ='" + name + "' order by b.START_DATE ";
        return find(sql);
    }

    /**
     * 查找平台费用(费用账单)
     *
     * @param param
     * @return
     */
    public JSONArray merchantCostPlatform1(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT b.id,\n" +
                "       (SELECT NAME FROM MAS_INCOME_TYPE WHERE a.INCOME_TYPE_ID = ID) INCOME_TYPE_NAME,\n" +
                "                  case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   a.AMOUNT * -1\n" +
                "                  else\n" +
                "                   a.AMOUNT\n" +
                "                end AMOUNT, " +
                "                 case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   a.recovery_amount * -1\n" +
                "                  else\n" +
                "                   a.recovery_amount\n" +
                "                end recovery_amount, " +
                "                 case\n" +
                "                  when (select C.id\n" +
                "                          from mas_party_role a, mas_role c\n" +
                "                         where b.PARTNER_ID = a.id\n" +
                "                           and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "                   a.freeze_amount * -1\n" +
                "                  else\n" +
                "                   a.freeze_amount\n" +
                "                end freeze_amount, " +
                "       (CASE WHEN b.SOURCE_BUSINESS_KEY = 'Expense' THEN '平台费用'\n" +
                "         WHEN b.SOURCE_BUSINESS_KEY = 'SettleIncome' THEN '平台定损'" +
                " WHEN b.SOURCE_BUSINESS_KEY = 'LossQuotation' THEN '定损单'\n" +
                "         WHEN b.SOURCE_BUSINESS_KEY = 'Voucher' THEN '业务凭证'\n" +
                "         WHEN b.SOURCE_BUSINESS_KEY = 'server' THEN '服务' ELSE\n" +
                "          b.SOURCE_BUSINESS_KEY END) SOURCE_BUSINESS_KEY, b.SOURCE,\n" +
                "       (CASE WHEN b.SOURCE_BUSINESS_TYPE = 'Expense' THEN\n" +
                "          '平台费用' WHEN b.SOURCE_BUSINESS_TYPE = 'SettleIncome' THEN\n" +
                "          '平台定损' " +
                " WHEN b.SOURCE_BUSINESS_KEY = 'LossQuotation' THEN '定损单' WHEN b.SOURCE_BUSINESS_TYPE = 'Voucher' THEN\n" +
                "          '业务凭证' WHEN b.SOURCE_BUSINESS_TYPE = 'server' THEN '服务' ELSE\n" +
                "       b.SOURCE_BUSINESS_TYPE END) SOURCE_BUSINESS_TYPE, (SELECT NAME FROM MAS_PARTY\n" +
                "       WHERE ID = (SELECT P.PARTY_ID FROM MAS_PARTY_ROLE P WHERE P.ID = b.CREATOR_ID)) CREATOR,\n" +
                "    a.AMOUNT  SETTLEMENT_AMOUNT,  CASE\n" +
                "         when (select C.id\n" +
                "                 from mas_party_role a, mas_role c\n" +
                "                where b.PARTNER_ID = a.id\n" +
                "                  and a.ROLE_ID = c.id) = 'Supplier' then\n" +
                "          (a.AMOUNT - a.recovery_amount - a.freeze_amount) * -1\n" +
                "         else\n" +
                "          a.AMOUNT - a.recovery_amount - a.freeze_amount\n" +
                "       end AMOUNTDEDUCTED," +
                "       TO_CHAR(b.CREATE_DATE, 'yyyy-mm-dd') AS CREATE_DATE,TO_CHAR(a.start_date, 'yyyy-mm-dd') AS start_date," +
                "TO_CHAR(a.end_date, 'yyyy-mm-dd') AS end_date,a.note FROM pur_income_item a\n" +
                "  left join pur_income b on a.income_id = b.id where  b.state <> 'sent' ");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS != null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE", DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END", DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (param.get("COMPANY_ID") != null) stringBuffer.append(" AND b.company_id  = :COMPANY_ID ");
        if (param.get("NAME") != null)
            stringBuffer.append(" and (SELECT NAME FROM MAS_INCOME_TYPE WHERE a.INCOME_TYPE_ID = ID) = :NAME ");
        if (param.get("DATE_OF_STATISTICS_STATE") != null) {
            try {
                if (Objects.isNull(param.getString("DATE_APPROVE"))) {
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    Date date = formatter.parse(param.getString("DATE_APPROVE"));
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
                }
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()) {
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate", SmonthOfLastDate);
                        stringBuffer.append(" AND b.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND b.CREATE_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate", SmonthOfFirstDate);
                        stringBuffer.append(" AND b.CREATE_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND b.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }
            }
        }
        return toJSONArray(find(stringBuffer.toString(), param));
    }
}
